How do I add conditional formatting to a range of Excel cells?

I have a DXL script which exports requirements to an Excel sheet, which is then sent for acceptance by certain people in our project. For that reason I have an attribute which can take only two values, Yes or No. When exporting that attribute I apply the Validation method so that only Yes or No can be added to that Excel column. That part works like a charm, I included that function for anyone interested (it took me days to find out how to do it...).

The second included function sets conditional formatting to the same cells, red font colour for No and green for Yes. My problem is that I can't get it to work. Conditional formatting is set, but with empty formatting. Any help is much appreciated. As you probably understand from the lines commented out, I have no real knowledge how to "translate" a VBA method to oleMethod.

Thank you in advance.
/Lennart
hell_se - Fri Jun 08 05:55:05 EDT 2012

Re: How do I add conditional formatting to a range of Excel cells?
Mathias Mamsch - Thu Jun 14 06:17:55 EDT 2012

From the examples on the web I see on the web you need to first add the FormatCondition which will return to you the condition object, and then set the colorindex on that. So (without testing it myself) it should be something like:
 

selectRange (objSheet, iTop, iLeft, iBottom, iRight, objRange)
oleGet (objRange, "FormatConditions", objConditional)
if (objConditional != null) {
    clear args
    put (args, "Type", 1)    // xlCellValue
    put (args, "Operator", 3) // xlEqual
    put (args, "Formula1", "Nej")
 
    OleAutoObj objRangeCondition
    // add the condition, will return the condition object
    debug oleMethod (objConditional, "Add", args, objRangeCondition)
    OleAutoObj objRangeConditionFont = null
    // get the font property 
    debug oleGet (objRangeCondition, "Font", objRangeConditionFont) 
    // set the color index on the font
    debug olePut (objRangeConditionFont, "ColorIndex", 3)
}

Maybe that helps, regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

Re: How do I add conditional formatting to a range of Excel cells?
hell_se - Thu Jun 14 06:52:21 EDT 2012

selectRange (objSheet, iTop, iLeft, iBottom, iRight, objRange)
oleGet (objRange, "FormatConditions", objConditional)
if (objConditional != null) {
    clear args
    put (args, "Type", 1)    // xlCellValue
    put (args, "Operator", 3) // xlEqual
    put (args, "Formula1", "Nej")
 
    OleAutoObj objRangeCondition
    // add the condition, will return the condition object
    debug oleMethod (objConditional, "Add", args, objRangeCondition)
    OleAutoObj objRangeConditionFont = null
    // get the font property 
    debug oleGet (objRangeCondition, "Font", objRangeConditionFont) 
    // set the color index on the font
    debug olePut (objRangeConditionFont, "ColorIndex", 3)
}